--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_art_gencod AS (SELECT * FROM &erp.ART_GENCOD WHERE 1=2);

/*

*/


DELETE FROM se_art_gencod;           /*339809*/    /*341066*/   /*3697*/

 INSERT INTO se_art_gencod                  /*213984*/
 (SELECT p.gencod gencod_no,
         0 enreg_id,
         c.comp_num comp_num,
         SubStr(p.gencod,1,6) cnuf_no,
         SubStr(p.gencod,7,6)cip_no,
         SubStr(p.gencod,13,1) clectrl_no,
         NULL libre1_val,
         NULL libre2_val,
         NULL libre3_val
    FROM uv_pproduits p, &erp.art_comp c
    WHERE p.numspecif=c.combsto_id
      AND p.gencod IS NOT NULL
      AND p.codgestion='3'
      ) ;

 INSERT INTO se_art_gencod              /*127082*/
 (SELECT c.gencod gencod_no,
         0 enreg_id,
         c.comp_num comp_num,
         SubStr(c.gencod,1,6) cnuf_no,
         SubStr(c.gencod,7,6)cip_no,
         SubStr(c.gencod,13,1) clectrl_no,
         'C' libre1_val,
         c.qtecol libre2_val,
         NULL libre3_val
    FROM uv_pgencodcolis_moli c,uv_pproduits p, &erp.art_comp c
    WHERE c.clergp=p.id
      aND p.numspecif=c.combsto_id
      AND p.gencod IS NOT NULL
     -- AND p.numspecif='01995073'
      AND p.codgestion='3') ;


 UPDATE se_art_gencod tf SET enreg_id=(select enreg_id FROM &erp.art_gencod f WHERE f.gencod_no=tf.gencod_no AND f.comp_num=tf.comp_num) ;
 UPDATE se_art_gencod tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 CALL uvse_synchroverserp('enreg_id','ART_GENCOD','O') ;



 commit;

 select * from (
SELECT &outils.stragg(source) source,gencod_no, enreg_id, comp_num, cnuf_no, cip_no, clectrl_no, libre1_val, libre2_val, libre3_val
FROM (
  SELECT 'uv' source ,g.* FROM se_art_gencod g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.art_gencod g
  )
GROUP BY gencod_no, enreg_id, comp_num, cnuf_no, cip_no, clectrl_no, libre1_val, libre2_val, libre3_val
HAVING Count(1)!=2) r

ORDER BY gencod_no ;